The present invention relates to the field of computer systems. More particularly, the invention relates to a method and system for improving the response time to execute a query involving a partitioned database object.
Database management systems are computer-based systems that manage and store information. In many database systems, users store, update, and retrieve information by submitting commands to a database application responsible for maintaining the database. In a traditional client-server architecture, the user is located at a client station while the database application resides at a server. With the widespread use of the internet, multi-tier database architectures are now common. In one approach to a multi-tier system, the user is given access through a user interface (e.g., a web browser) at a user station, the user interface sends information requests to a middle-tier server (e.g., a web server), and the web server in turn handles the retrieval and packaging of data from one or more back-end database servers. The packaged information xe2x80x9cpagexe2x80x9d is thereafter sent to the user interface for display to the user.
As advances are made to computer hardware and software systems, the quantity of data managed by database systems is increasingly becoming larger. Presently available database systems, such as the Oracle 8i product from Oracle Corporation of Redwood Shores, Calif., are capable of supporting many terabytes of data right out of the box.
The drawback with implementing a database having a very large amount of data is it is that, all else being equal, a query against a large set of data normally has a worse response time than the same query against a smaller set of data. In one approach to executing a database query, the entire body of relevant data (or relevant indexes) in the database is processed to produce a responsive reply to the query. Thus, the slower response times of a query against a larger body of data can be directly attributed to the larger quantities of data that are accessed to satisfy the database query. As larger databases are increasingly being proliferated, greater levels of delays will exist for users seeking to query these larger databases. These delays could become an exasperating source of delays and inefficiencies for organizations that use large database systems.
An example of this problem exists with respect to the typical internet search engine. Internet search engines maintain a searchable database of web sites that may be indexed in various ways. With the numbers of internet web sites explosively increasing in recent years, the amount of web site data maintained and searched by the internet search engine has correspondingly increased. The larger sets of data that must be searched to satisfy a search request could create increasingly large delays before search results are returned to the user by the search engine. Since many search engines performs a sorting operation upon the search results (e.g., based upon the number of times a search term appears on a web site), all of the responsive data in the larger body of information maintained by the search engine may be searched and sorted to ensure a correct order for the search results before any results are presented to the user. This is particularly frustrating for users that only wish to view the most relevant information from the first few pages of the search results, rather than the less useful information that often appears on the nth page returned by the search engine.
A query may seek to access a partitioned database object. Partitioning in a database system generally refers to the process of decomposing an object into a greater number of relatively smaller objects. Smaller objects are often easier to manage and more efficient to search than larger objects. Thus, database systems utilize partitioning to decompose objects such as tables and indexes into smaller and more manageable pieces or xe2x80x9cpartitions.xe2x80x9d
The invention provide a method and mechanism to improve the response time of a query that is executed against a partitioned database object. One disclosed embodiment of the invention is particularly applicable to queries that involve relative ranking or ordering of results to be computed across multiple partitions of a partitioned database table. Since evaluation of predicates on a partitioned object typically involves iterating over one partition at a time, evaluating such queries normally requires significant overhead to retrieve, store, and sort data, as well as delays resulting from blocking the query results until all the partitions have been processed. An embodiment of the present invention addresses this problem by accessing only portions of the partitions during each pass through the partitions, processing only the retrieved portions of the partitions, and immediately returning results to the query. These steps are repeated until all of the results needed to satisfy the query has been provided. Further details of aspects, objects, and advantages of the invention are described below in the detailed description, drawings, and claims.